package a_o2odata_deal

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2019/11/28 14:43
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object yushou_main {
  def main(args: Array[String]): Unit = {
    val conf = new SparkConf()
    conf.setAppName(s"${this.getClass.getSimpleName}")
    conf.set("spark.debug.maxToStringFields", "500")
    //conf.setMaster("local[*]")
    conf.set("es.nodes", "192.168.1.157")
    conf.set("es.port", "9200")
    conf.set("cluster.name", "O2OElastic")
    conf.set("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
    conf.set("spark.sql.caseSensitive", "true")
    val sc = new SparkContext(conf)
    val sqlContext = new SQLContext(sc)
    sc.setLogLevel("WARN")
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    //sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")

    //加载双十一原始数据
    val yushou_data = sqlContext.read.json("s3a://o2o-sourcedata/obs-source-2019/shuang11/presale/tmall/last_yushou/tmall_detail_shuang11_yushou/")
    //取出预售数据中销量减去10月31号的销量大于0的数据
    yushou_data.createOrReplaceTempView("yushou_data")
    sqlContext.sql(
      """
        |select
        |good_id,
        |preSaleCount,
        |rank_desc,
        |rank_asc
        |from
        |(select
        |good_id,
        |preSaleCount,
        |row_number() over(partition by good_id order by crawl_date desc) as rank_desc,
        |row_number() over(partition by good_id order by crawl_date ) as rank_asc
        |from
        |(select
        |*
        |from
        |(select
        |good_id,
        |IFNULL(explode_field.`crawl_date`,"-1") crawl_date,
        |IFNULL(explode_field.`preSaleCount`,"-1") preSaleCount
        |from
        |(select
        |*,
        |explode(add_to_field) as explode_field
        |from
        |yushou_data))
        |where crawl_date>'1572451200'))
        |where rank_asc=1 or rank_desc=1
      """.stripMargin).createOrReplaceTempView("middle_data")

   val yushou = sqlContext.sql(
      s"""
         |select
         |t1.good_id,
         |cast(t1.preSaleCount as bigint)-cast(t2.preSaleCount as bigint) as preSaleCount
         |from
         |(select * from middle_data where rank_desc=1) t1
         |inner join
         |(select * from middle_data where rank_asc=1) t2
         |on t1.good_id=t2.good_id
       """.stripMargin).where("preSaleCount>0")

    yushou.repartition(1).write.json("s3a://o2o-dataproces-group/panzonghao/last_yushou_month11")

    print(yushou.count()+"\n")
    yushou.show(200,false)


  }


}
